﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace DingChiServiceApp
{
    /// <summary>
    /// sql server helper
    /// </summary>
    public class SqlServerHelper : DbHelper
    {
        private SqlConnection connection;
        // 
        //异常:
        //
        //   T:System.InvalidOperationException:
        //     Cannot open a connection without specifying a data source or server.
        //
        //   T:Sql.Data.SqlClient.SqlException:
        //     A connection-level error occurred while opening the connection.
        //
        /// <summary>
        /// 连接对像
        /// </summary>
        public SqlConnection Connection
        {
            get
            {
                if (connection == null)
                {
                    connection = new SqlConnection(connectionString);
                    if (connection.State == ConnectionState.Closed)
                    {
                        try
                        {
                            connection.Open();
                        }
                        catch (Exception e)
                        {
                            throw new Exception("Sql server 数据打开失败！:" + e.Message);
                        }
                    }
                    return connection;
                }
                else
                {
                    if (connection.State == ConnectionState.Closed)
                    {
                        try
                        {
                            connection.Open();
                        }
                        catch (Exception e)
                        {
                            throw new Exception("Sql server 数据打开失败！:" + e.Message);
                        }
                    }
                    return connection;
                }
            }
        }


        /// <summary>
        /// 构造方法
        /// </summary>
        public SqlServerHelper(string connstring)
        {
            if (!string.IsNullOrEmpty(connstring))
            {
                connectionString = connstring;
            }
            if (connectionString == null || connectionString.Length <= 0)
            {
                throw new Exception("Sql server 数据没有正常的配制！");
            }
        }

        /// <summary>
        /// 检查连接能否打开
        /// </summary>
        /// <param name="connstring"> 连接字串</param>
        /// <returns></returns>
        public override bool CheckConn(string connstring = null)
        {
            if (String.IsNullOrEmpty(connstring))
            {
                connstring = connectionString;
            }
            using (SqlConnection conn = new SqlConnection(connstring))
            {
                try
                {
                    conn.Open();
                    return conn.State == ConnectionState.Open;
                }
                catch (Exception)
                {
                    return false;
                }
            }
        }

        /// <summary>
        /// 获取表结构
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbname"> 库名</param>
        /// <returns></returns>
        public  List<T> GetAllTableSchema<T>(string dbname)
        {
            //List<T> dss;
            //string sql = $"SELECT TABLE_NAME as tableName,TABLE_COMMENT as tableComment,CREATE_TIME as createTime,UPDATE_TIME as updateTime ,TABLE_ROWS as tableRows,DATA_LENGTH as dataLength   from information_schema.tables where table_schema='{dbname}' and (table_type='base table' or table_type='BASE TABLE');";
            //DataTable dt = this.ExcuteDataTable(sql, null);
            //string json = JsonHelper.ObjectToJson(dt);
            //dss = (List<T>)JsonHelper.JsonToObject(json, typeof(List<T>));
            //return dss;
            throw new NotImplementedException();
        }

        /// <summary>
        /// judge the table is or not exist
        /// </summary>
        /// <param name="dbName"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public override bool ExistTable(string dbName, string table)
        {
            //if (string.IsNullOrEmpty(dbName) || string.IsNullOrEmpty(table))
            //{
            //    return false;
            //}
            //string sql = $"SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = '{dbName}' and table_name ='{table}' and table_type='base table' ;";
            //DataTable dt = this.ExcuteDataTable(sql, null);
            //if (dt.Rows.Count > 0)
            //{
            //    return true;
            //}
            //else
            //{
            //    return false;
            //}
            throw new NotImplementedException();
        }

        /// <summary>
        /// get the schema of table
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public  List<T> GetTableSchema<T>(string tableName)

        {
            if (string.IsNullOrEmpty(tableName))
            {
                return null;
            }
            List<T> ts;
            string sql = string.Format(" desc {0};", tableName);
            DataTable dt = this.ExcuteDataTable(sql, null);
            String json = JsonHelper.ObjectToJson(dt);
            ts = (List<T>)JsonHelper.JsonToObject(json, typeof(List<T>));
            return ts;
        }

        /// <summary>
        /// 数据表的结构语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public override string GetCreateSql(string tableName)
        {
            string sql = $"show create table {tableName};";
            DataTable dt = this.ExcuteDataTable(sql, null);
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0][1].ToString();
            }
            return null;
        }


        /// <summary>
        /// 按SQl语句查询 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns>DataTable</returns>
        public override List<T> Select<T>(string sql)
        {
            DataTable dt = ExcuteDataTable(sql);
            Type type = typeof(T);
            List<T> list = new List<T>();
            FieldInfo[] pArray = type.GetFields();
            foreach (DataRow row in dt.Rows)
            {
                T t = ParseEntityWithLog<T>(row, pArray);
                list.Add(t);
            }
            dt?.Dispose();
            return list;
        }

        /// <summary>  
        /// 查询  
        /// </summary>  
        /// <param name="sql">要执行的sql语句</param>  
        /// <param name="parameters">所需参数</param>  
        /// <returns>结果DataTable</returns>  
        public DataTable ExcuteDataTable(string sql, SqlParameter[] parameters)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(sql, conn))
                {
                    PrintSql(sql);
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                    {
                        adapter.Fill(dt);
                    }
                    return dt;
                }
            }
        }
        /// <summary>  
        /// 查询  
        /// </summary>  
        /// <param name="sql">要执行的sql语句</param>  
        /// <returns>结果DataTable</returns>  
        public override DataTable ExcuteDataTable(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    if (conn.State != ConnectionState.Open) conn.Open();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                using (SqlCommand command = conn.CreateCommand())
                {
                    DataTable dt = new DataTable();
                    command.CommandText = sql;
                    PrintSql(sql);
                    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                    {
                        adapter.Fill(dt);
                        adapter.Dispose();
                    }
                    return dt;
                }
            }
        }

        #region 增删改  

        /// <summary>  
        /// 增删改  
        /// </summary>  
        /// <param name="sql">要执行的sql语句</param>  
        /// <param name="parametes">所需参数</param>  
        /// <returns>所受影响的行数</returns>  
        public int ExecuteNonQuery(string sql, SqlParameter[] parametes)
        {
            int affectedRows = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    if (conn.State != ConnectionState.Open) conn.Open();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                SqlTransaction transation = conn.BeginTransaction();
                try
                {

                    using (SqlCommand command = conn.CreateCommand())
                    {
                        command.Transaction = transation;
                        command.CommandText = sql;
                        PrintSql(sql);
                        if (parametes != null)
                        {
                            command.Parameters.AddRange(parametes);
                        }
                        affectedRows = command.ExecuteNonQuery();
                        transation.Commit();
                    }
                }

                catch (Exception e)
                {
                    transation.Rollback();
                    throw e;
                }
                finally
                {
                    Connection.Close();
                }
            }
            return affectedRows;
        }
        /// <summary>
        /// 事务处理多条多条操作
        /// </summary>
        /// <param name="sqls"></param>
        /// <returns></returns>
        public override int TransactionExecute(params string[] sqls)
        {
            int affectedRows = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    if (conn.State != ConnectionState.Open) conn.Open();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                SqlTransaction transation = conn.BeginTransaction();
                try
                {
                    using (SqlCommand command = conn.CreateCommand())
                    {
                        //command.Connection = Connection;
                        command.Transaction = transation;
                        for (int i = 0; i < sqls.Length; i++)
                        {
                            PrintSql(sqls[i]);
                            command.CommandText = sqls[i];
                            affectedRows += command.ExecuteNonQuery();
                        }
                        transation.Commit();
                    }
                }
                catch (Exception)
                {
                    affectedRows = 0;
                    transation.Rollback();
                }
                finally
                {
                    transation.Commit();
                    conn.Close();
                }
            }
            return affectedRows;
        }


        /// <summary>
        /// 执行删除语句
        /// </summary>
        /// <param name="sql">删除语句</param>
        /// <returns>影响行数</returns>
        public override int Delete(string sql)
        {
            return ExecuteNonQuery(sql, null);
        }
        /// <summary>
        /// 删除对像 ，支持软件删除
        /// </summary>
        /// <typeparam name="T">对像类型</typeparam>
        /// <param name="obj">对像</param>
        /// <param name="isTrueDelete">是否真的删除 默认软删除</param>
        /// <returns>影响行数</returns>
        public override int Delete<T>(T obj, Boolean isTrueDelete = true)
        {
            string deleteSql = SqlBuilder.GetDeleteSql(obj, isTrueDelete);
            return ExecuteNonQuery(deleteSql, null);
        }

        /// <summary>
        /// 执行修改语句
        /// </summary>
        /// <param name="sql">删除语句</param>
        /// <returns>影响行数</returns>
        public override int Update(string sql)
        {
            return ExecuteNonQuery(sql, null);
        }
        /// <summary>
        /// 修改对像
        /// </summary>
        /// <typeparam name="T">对像类型</typeparam>
        /// <param name="obj">对像</param>
        /// <returns>影响行数</returns>
        public override int Update<T>(T obj)
        {
            string sql = SqlBuilder.GetUpdateSql(obj);
            return ExecuteNonQuery(sql, null);
        }

        /// <summary>
        /// 执行插入语句
        /// </summary>
        /// <param name="sql">插入语句</param>
        /// <returns>影响行数</returns>
        public override int Insert(string sql)
        {
            return ExecuteNonQuery(sql, null);
        }
        /// <summary>
        /// 插入对像
        /// </summary>
        /// <typeparam name="T">对像类型</typeparam>
        /// <param name="obj">对像</param>
        /// <returns>影响行数</returns>
        public override int Insert<T>(T obj)
        {
            string insertSql = SqlServerSqlBulider.GetInsertSql(obj);
            return ExecuteNonQuery(insertSql, null);
        }

        /// <summary>
        /// 插入或者更新对像
        /// </summary>
        /// <typeparam name="T">对像类型</typeparam>
        /// <param name="obj">对像</param>
        /// <returns>影响行数</returns>
        public override int InsertOrUpdate<T>(T obj)
        {
            if (CheckExist(obj))
            {
                return this.Update(obj);
            }
            else
            {
                return this.Insert(obj);
            }
        }

        #endregion

        /// <summary>
        /// 检查对像是已经存在于数据库中
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        /// <returns></returns>
        public override bool CheckExist<T>(T obj)
        {
            Type type = typeof(T);
            try
            {
                PropertyInfo propertyinfo = type.GetProperty("id");
                if (propertyinfo == null)
                {
                    propertyinfo = type.GetProperty("Id");
                    if (propertyinfo == null)
                    {
                        throw new Exception(SqlBuilder.buildSqlErrorMessage);
                    }
                }
                object tempObj = propertyinfo.GetValue(obj, null);
                if (tempObj == null || tempObj.ToString().Length <= 0)
                {
                    throw new Exception(SqlBuilder.buildSqlErrorMessage);
                }
                string condition = SqlBuilder.splitChar + "id" + SqlBuilder.splitChar + "=" + SqlBuilder.valueSplitChar + tempObj.ToString() + SqlBuilder.valueSplitChar;
                string sql = SqlBuilder.GetSelectSql(SqlBuilder.GetTableName(obj), null, condition);
                DataTable dt = this.ExcuteDataTable(sql);
                if (dt != null && dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch
            {
                throw;
            }
            //put code in up 
        }
        /// <summary>
        /// 是否正在连接
        /// </summary>
        /// <returns></returns>
        public override bool IsConnecting()
        {
            return Connection.State == ConnectionState.Connecting;
        }
        /// <summary>
        /// 连接是否已经打开
        /// </summary>
        /// <returns></returns>
        public override bool IsOpened()
        {
            return Connection.State == ConnectionState.Open;
        }
        /// <summary>
        /// 根据Id查找对像
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns> 对像  or null</returns>
        public override T FindById<T>(int id)
        {
            object t = Activator.CreateInstance(typeof(T));
            string tableName = StringHelper.CamelCaseToDBnameing(t.GetType().Name);
            String sql = SqlBuilder.GetSelectSql(tableName, null, "id = " + id);
            return Find<T>(sql);
        }

        /// <summary>
        /// 根据Sql 语句查找一个对像
        /// </summary>
        /// <typeparam name="T">要查找一个对像</typeparam>
        /// <param name="sql">Sql 语句</param>
        /// <returns></returns>
        public override T Find<T>(string sql)
        {
            DataTable dt = ExcuteDataTable(sql);
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            var row = dt.Rows[0];
            return ParseEntityWithLog<T>(row);
        }

        /// <summary>
        /// 执行sql 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>影响行数</returns>
        public override int ExecuteNonQuery(string sql)
        {
            int affectedRows = 0;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    if (connection.State != ConnectionState.Open) connection.Open();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                try
                {
                    using (SqlCommand command = connection.CreateCommand())
                    {
                        command.CommandText = sql;
                        affectedRows = command.ExecuteNonQuery();
                        PrintSql(sql);
                    }
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
            return affectedRows;
        }

        /// <summary>
        /// 数据库存是否存在
        /// </summary>
        /// <param name="databaseName">名称</param>
        /// <returns></returns>
        public override bool ExistDatabase(string databaseName)
        {
            //if (string.IsNullOrEmpty(databaseName)) return false;
            //string sql = $"SELECT * FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = '{databaseName}';";
            //var data = ExcuteDataTable(sql);
            //return (data != null && data.Rows.Count > 0);
            throw new NotImplementedException();
        }

        /// <summary>
        /// 创建数据库
        /// </summary>
        /// <param name="databaseName">名称</param>
        /// <returns></returns>
        public override bool CreateDatabase(string databaseName)
        {
            string sql = $"CREATE DATABASE IF NOT EXISTS `{databaseName}`";
            int affectedRows = ExecuteNonQuery(sql);
            return affectedRows > 0;
        }


        /// <summary>
        /// 统计条数
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        public override int Count(string tableName, string condition)
        {
            string sql = $"SELECT count(*)  as num from  `{tableName}`";
            DataTable dt = ExcuteDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                var row = dt.Rows[0];
                int res = Convert.ToInt32(row["num"]);
                return res;
            }
            return 0;
        }



    }
}
